package persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import modeloDatos.Cliente;


public class DAOBusqueda {

	private Connection conn;
	
	public DAOBusqueda() {
		
	}
	
	public void finalize(){
        ConnectionManager.getInstance().checkIn(conn);
    }
	
	public List<String> recuperarIntereses(){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT * FROM intereses";
        ResultSet result = null;
        List<String> listaI = new LinkedList<String>();
		try {
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();
			while (result.next()) {
				String s = result.getString("descripcion");
				listaI.add(s);
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return listaI;
	}
	
	public List<String> recuperarServicios(){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT * FROM tiposservicio";
        ResultSet result = null;
        List<String> listaS = new LinkedList<String>();
		try {
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();
			while (result.next()) {
				String s = result.getString("descripcion");
				listaS.add(s);
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return listaS;
	}
	
	public Map<Integer,String> mapaEntidadesJuridicas(){
		Map<Integer,String> entidades = new HashMap<Integer,String>();
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT * FROM entidadjuridica";
        ResultSet result = null;
		try {
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();
			while (result.next()) {
				String s = result.getString("descripcion");
				int id = result.getInt("idEntidadJuridica");
				entidades.put(id, s);
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		
		return entidades;
	}
	
	public List<String> recuperarEntidadesJuridicas(){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT * FROM entidadjuridica";
        ResultSet result = null;
        List<String> listaE = new LinkedList<String>();
		try {
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();
			while (result.next()) {
				String s = result.getString("descripcion");
				listaE.add(s);
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return listaE;
	}
	
	public Map<Integer,String> mapaTiposEmpresa(){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT * FROM tiposempresa";
        ResultSet result = null;
        Map<Integer,String> empresas = new HashMap<Integer,String>();
		try {
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();
			while (result.next()) {
				String s = result.getString("descripcion");
				int id = result.getInt("idTipoEmpresa");
				empresas.put(id, s);
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return empresas;
	}
	
	public List<String> recuperarTiposEmpresa(){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT * FROM tiposempresa";
        ResultSet result = null;
        List<String> listaT = new LinkedList<String>();
		try {
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();
			while (result.next()) {
				String s = result.getString("descripcion");
				listaT.add(s);
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return listaT;
	}
	
	public List<Cliente> buscarPersonaJuridica(Boolean pJ){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql;
		if(pJ){
			sql = "SELECT * FROM Clientes WHERE cif";
		} else{
			sql = "SELECT * FROM Clientes WHERE nif";
		}
        ResultSet result = null;
        List<Cliente> listaC = new LinkedList<Cliente>();
        try {
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();
			while (result.next()) {
				Cliente c = new Cliente(result.getInt("idClienteEmpresa"));
				c.setNombre(result.getString("nombre"));
				c.setApellidos(result.getString("apellidos"));
				c.setCif(result.getString("cif"));
				c.setNif(result.getString("nif"));
				c.setDireccion(result.getString("direccion"));
				c.setCp(result.getInt("cp"));
				c.setMunicipio(result.getString("municipio"));
				c.setProvincia(result.getString("provincia"));
				c.setTelefFijo(result.getInt("telefonoFijo"));
				c.setTelefMovil(result.getInt("telefonoMovil"));
				c.setFax(result.getInt("fax"));
				c.setEmail(result.getString("email"));
				c.setSsempresa(result.getString("ssempresa"));
				c.setPotencial(result.getBoolean("potencial"));
				c.setIdEntidadJuridica(result.getInt("idEntidadJuridica"));
				c.setIdTipoEmpresa(result.getInt("idTipoEmpresa"));
				c.setTipoCaptacion(result.getString("tipoCaptacion"));
				c.setObservaciones(result.getString("observaciones"));
				listaC.add(c);
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
        return listaC;
	}
	
	public List<Cliente> buscarClienteInteres(String conjuntoIntereses){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql;
		sql = "SELECT DISTINCT cli.idClienteEmpresa, cli.nombre, cli.apellidos, cli.cif, cli.nif, cli.direccion, cli.cp, cli.municipio, cli.provincia, cli.telefonoFijo, cli.telefonoMovil, cli.fax, cli.email, cli.ssempresa, cli.potencial, cli.idEntidadJuridica, cli.idTipoEmpresa, cli.tipoCaptacion, cli.observaciones FROM clientes cli, clienteinteres clii, intereses i WHERE cli.idClienteEmpresa=clii.idClienteEmpresa AND clii.idInteres=i.idInteres AND i.descripcion IN ("+conjuntoIntereses.toString()+")";
        ResultSet result = null;
        List<Cliente> listaC = new LinkedList<Cliente>();
        try {
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();
			while (result.next()) {
				Cliente c = new Cliente(result.getInt("idClienteEmpresa"));
				c.setNombre(result.getString("nombre"));
				c.setApellidos(result.getString("apellidos"));
				c.setCif(result.getString("cif"));
				c.setNif(result.getString("nif"));
				c.setDireccion(result.getString("direccion"));
				c.setCp(result.getInt("cp"));
				c.setMunicipio(result.getString("municipio"));
				c.setProvincia(result.getString("provincia"));
				c.setTelefFijo(result.getInt("telefonoFijo"));
				c.setTelefMovil(result.getInt("telefonoMovil"));
				c.setFax(result.getInt("fax"));
				c.setEmail(result.getString("email"));
				c.setSsempresa(result.getString("ssempresa"));
				c.setPotencial(result.getBoolean("potencial"));
				c.setIdEntidadJuridica(result.getInt("idEntidadJuridica"));
				c.setIdTipoEmpresa(result.getInt("idTipoEmpresa"));
				c.setTipoCaptacion(result.getString("tipoCaptacion"));
				c.setObservaciones(result.getString("observaciones"));
				listaC.add(c);
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
        return listaC;
	}
	
	public List<Cliente> buscarClienteServicio(String conjuntoServicio){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql;
		sql = "SELECT DISTINCT cli.idClienteEmpresa, cli.nombre, cli.apellidos, cli.cif, cli.nif, cli.direccion, cli.cp, cli.municipio, cli.provincia, cli.telefonoFijo, cli.telefonoMovil, cli.fax, cli.email, cli.ssempresa, cli.potencial, cli.idEntidadJuridica, cli.idTipoEmpresa, cli.tipoCaptacion, cli.observaciones FROM clientes cli, presupuesto pres, serviciospresupuesto servp, tiposservicio tserv WHERE  cli.idClienteEmpresa=pres.idClienteEmpresa AND pres.idPresupuesto=servp.idPresupuesto AND servp.idTipoServicio=tserv.idTipoServicio AND tserv.descripcion IN ("+conjuntoServicio.toString()+")";
        ResultSet result = null;
        List<Cliente> listaC = new LinkedList<Cliente>();
        try {
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();
			while (result.next()) {
				Cliente c = new Cliente(result.getInt("idClienteEmpresa"));
				c.setNombre(result.getString("nombre"));
				c.setApellidos(result.getString("apellidos"));
				c.setCif(result.getString("cif"));
				c.setNif(result.getString("nif"));
				c.setDireccion(result.getString("direccion"));
				c.setCp(result.getInt("cp"));
				c.setMunicipio(result.getString("municipio"));
				c.setProvincia(result.getString("provincia"));
				c.setTelefFijo(result.getInt("telefonoFijo"));
				c.setTelefMovil(result.getInt("telefonoMovil"));
				c.setFax(result.getInt("fax"));
				c.setEmail(result.getString("email"));
				c.setSsempresa(result.getString("ssempresa"));
				c.setPotencial(result.getBoolean("potencial"));
				c.setIdEntidadJuridica(result.getInt("idEntidadJuridica"));
				c.setIdTipoEmpresa(result.getInt("idTipoEmpresa"));
				c.setTipoCaptacion(result.getString("tipoCaptacion"));
				c.setObservaciones(result.getString("observaciones"));
				listaC.add(c);
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
        return listaC;
	}
	
	public List<Cliente> buscarClienteEntidades(String conjuntoEntidades){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql;
		sql = "SELECT DISTINCT cli.idClienteEmpresa, cli.nombre, cli.apellidos, cli.cif, cli.nif, cli.direccion, cli.cp, cli.municipio, cli.provincia, cli.telefonoFijo, cli.telefonoMovil, cli.fax, cli.email, cli.ssempresa, cli.potencial, cli.idEntidadJuridica, cli.idTipoEmpresa, cli.tipoCaptacion, cli.observaciones FROM clientes cli, entidadjuridica ej WHERE cli.idEntidadJuridica=ej.idEntidadJuridica AND ej.descripcion IN ("+conjuntoEntidades.toString()+")";
        ResultSet result = null;
        List<Cliente> listaC = new LinkedList<Cliente>();
        try {
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();
			while (result.next()) {
				Cliente c = new Cliente(result.getInt("idClienteEmpresa"));
				c.setNombre(result.getString("nombre"));
				c.setApellidos(result.getString("apellidos"));
				c.setCif(result.getString("cif"));
				c.setNif(result.getString("nif"));
				c.setDireccion(result.getString("direccion"));
				c.setCp(result.getInt("cp"));
				c.setMunicipio(result.getString("municipio"));
				c.setProvincia(result.getString("provincia"));
				c.setTelefFijo(result.getInt("telefonoFijo"));
				c.setTelefMovil(result.getInt("telefonoMovil"));
				c.setFax(result.getInt("fax"));
				c.setEmail(result.getString("email"));
				c.setSsempresa(result.getString("ssempresa"));
				c.setPotencial(result.getBoolean("potencial"));
				c.setIdEntidadJuridica(result.getInt("idEntidadJuridica"));
				c.setIdTipoEmpresa(result.getInt("idTipoEmpresa"));
				c.setTipoCaptacion(result.getString("tipoCaptacion"));
				c.setObservaciones(result.getString("observaciones"));
				listaC.add(c);
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
        return listaC;
	}
	
	public List<Cliente> buscarClienteTipos(String conjuntoTipos){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql;
		sql = "SELECT DISTINCT cli.idClienteEmpresa, cli.nombre, cli.apellidos, cli.cif, cli.nif, cli.direccion, cli.cp, cli.municipio, cli.provincia, cli.telefonoFijo, cli.telefonoMovil, cli.fax, cli.email, cli.ssempresa, cli.potencial, cli.idEntidadJuridica, cli.idTipoEmpresa, cli.tipoCaptacion, cli.observaciones FROM clientes cli, tiposempresa t WHERE cli.idTipoEmpresa=t.idTipoEmpresa AND t.descripcion IN ("+conjuntoTipos.toString()+")";
        ResultSet result = null;
        List<Cliente> listaC = new LinkedList<Cliente>();
        try {
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();
			while (result.next()) {
				Cliente c = new Cliente(result.getInt("idClienteEmpresa"));
				c.setNombre(result.getString("nombre"));
				c.setApellidos(result.getString("apellidos"));
				c.setCif(result.getString("cif"));
				c.setNif(result.getString("nif"));
				c.setDireccion(result.getString("direccion"));
				c.setCp(result.getInt("cp"));
				c.setMunicipio(result.getString("municipio"));
				c.setProvincia(result.getString("provincia"));
				c.setTelefFijo(result.getInt("telefonoFijo"));
				c.setTelefMovil(result.getInt("telefonoMovil"));
				c.setFax(result.getInt("fax"));
				c.setEmail(result.getString("email"));
				c.setSsempresa(result.getString("ssempresa"));
				c.setPotencial(result.getBoolean("potencial"));
				c.setIdEntidadJuridica(result.getInt("idEntidadJuridica"));
				c.setIdTipoEmpresa(result.getInt("idTipoEmpresa"));
				c.setTipoCaptacion(result.getString("tipoCaptacion"));
				c.setObservaciones(result.getString("observaciones"));
				listaC.add(c);
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
        return listaC;
	}
}
